<?php
/**
 * Created by PhpStorm.
 * User: admin
 * Date: 2016/11/30
 * Time: 11:45
 */

require 'env.php';

header('Content-Type: text/html;charset=utf-8');
$start_date = $_GET['start_date'];
$end_date = $_GET['end_date'];

if ($start_date == '' || $end_date == '') {
    echo '开始时间或者结束时间都不能为空';
    exit;
}

$start_time = date("Y-m-d H:i:s", strtotime($start_date));
$end_time = date("Y-m-d H:i:s", strtotime($end_date) + (23 * 3600) + (59 * 60) + 59);

if (strtotime($start_time) > strtotime($end_time)) {
    echo '开始时间不能小于结束时间';
    exit;
}


if ((strtotime($end_time) - strtotime($start_time)) > 30 * 24 * 3600) {
    echo '查询周期不能操作30天';
    exit;
}

$search = " WHERE createtime >= '$start_time' and createtime <= '$end_time'";
$orders = getAllDataBySearch($search);
foreach ($orders as $orde) {
    $new['num'] = $orde['num'];
    $new['status'] = $orde['status'];
    $new['order_time'] = $orde['order_time'];
    $new['owner_addr'] = $orde['owner_addr'];
    $new['createtime'] = $orde['createtime'];
    $news[] = $new;
}
require_once dirname(__FILE__) . '/lib/PHPExcelClasses/PHPExcel.php';
$fileName_s = "快递状态表";
$headArr_s = array(
    '快递单号',
    '快递状态',
    '录单时间',
    '处理营业所',
    '快递完成时间');
$data_source = $news;
getExcel($fileName_s, $headArr_s, $data_source);

function getExcel($fileName, $headArr, $data)
{
    if (empty($data) || !is_array($data)) {
        die("data must be a array");
    }
    if (empty($fileName)) {
        exit;
    }
    $date = date("Y-m-d", time());
    $fileName .= "_{$date}.xlsx";

    //创建新的PHPExcel对象
    $objPHPExcel = new PHPExcel();
    $objProps = $objPHPExcel->getProperties();

    //设置表头
    $key = ord("A");
    foreach ($headArr as $v) {
        $colum = chr($key);
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue($colum . '1', $v);
        $key += 1;
    }

    $column = 2;
    $objActSheet = $objPHPExcel->getActiveSheet();
    foreach ($data as $key => $rows) { //行写入
        $span = ord("A");
        foreach ($rows as $keyName => $value) {// 列写入
            $j = chr($span);
            $objActSheet->setCellValue($j . $column, $value);
            $span++;
        }
        $column++;
    }

    $fileName = iconv("utf-8", "gb2312", $fileName);
//重命名表
    $objPHPExcel->getActiveSheet()->setTitle('Simple');
//设置活动单指数到第一个表,所以Excel打开这是第一个表
    $objPHPExcel->setActiveSheetIndex(0);
//将输出重定向到一个客户端web浏览器(Excel2007)
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header("Content-Disposition: attachment; filename=\"$fileName\"");
    header('Cache-Control: max-age=0');
    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');

    $objWriter->save('php://output'); //文件通过浏览器下载

    exit;
}


function getAllDataBySearch($search)
{
    try {
        $dsn = 'mysql:dbname=' . DB_NAME . ';host=' . DB_HOST . ';port=' . DB_PORT;
        $user = DB_USER;
        $password = DB_PWD;
        $dbh = new PDO($dsn, $user, $password);
        $dbh->query("set names utf8");

        $sql = "select * from bd_data_logs $search";

        $allres = $dbh->query($sql)->fetchAll();
        return $allres;
    } catch (PDOException $e) {
        echo $e->getMessage();
    }
}
